"""VisitorSheet repository implementation (Synchronous)."""

from __future__ import annotations

from datetime import datetime, date
from typing import Any

from sqlalchemy import func, extract
from sqlalchemy.orm import Session, joinedload

from ..models.visitor_sheet import VisitorSheet
from ..models.enums import IntentionLevel
from .base import Repository
from ..schemas.visitor_sheet import VisitorSheetCreate, VisitorSheetUpdate


class VisitorSheetRepository(Repository[VisitorSheet, VisitorSheetCreate, VisitorSheetUpdate]):
    """Repository for VisitorSheet operations (Synchronous)."""

    def __init__(self, session: Session) -> None:
        super().__init__(VisitorSheet, session)

    def get_by_lead(self, lead_id: int, skip: int = 0, limit: int = 100) -> list[VisitorSheet]:
        """Get visitor sheets by lead ID."""
        return (
            self.session.query(VisitorSheet)
            .filter(VisitorSheet.lead_id == lead_id)
            .order_by(VisitorSheet.visit_date.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )

    def get_by_property(
        self, property_id: int, skip: int = 0, limit: int = 100
    ) -> list[VisitorSheet]:
        """Get visitor sheets by property ID."""
        return (
            self.session.query(VisitorSheet)
            .filter(VisitorSheet.property_id == property_id)
            .order_by(VisitorSheet.visit_date.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )

    def get_by_agent(self, agent_id: int, skip: int = 0, limit: int = 100) -> list[VisitorSheet]:
        """Get visitor sheets by agent ID."""
        return (
            self.session.query(VisitorSheet)
            .filter(VisitorSheet.agent_id == agent_id)
            .order_by(VisitorSheet.visit_date.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )

    def count_by_criteria(
        self,
        lead_id: int | None = None,
        property_id: int | None = None,
        agent_id: int | None = None,
        start_date: date | None = None,
        end_date: date | None = None,
        intention: IntentionLevel | None = None,
    ) -> int:
        """Count visitor sheets by criteria."""
        query = self.session.query(func.count(VisitorSheet.id))

        if lead_id is not None:
            query = query.filter(VisitorSheet.lead_id == lead_id)
        if property_id is not None:
            query = query.filter(VisitorSheet.property_id == property_id)
        if agent_id is not None:
            query = query.filter(VisitorSheet.agent_id == agent_id)
        if start_date is not None:
            query = query.filter(VisitorSheet.visit_date >= start_date)
        if end_date is not None:
            query = query.filter(VisitorSheet.visit_date <= end_date)
        if intention is not None:
            query = query.filter(VisitorSheet.intention == intention)

        result = query.scalar()
        return int(result) if result is not None else 0

    def get_monthly_stats(
        self, year: int, month: int, agent_id: int | None = None
    ) -> dict[str, int]:
        """Get monthly visitor sheet statistics."""
        query = self.session.query(
            func.count(VisitorSheet.id).label("total_visits"),
            func.avg(VisitorSheet.satisfaction).label("avg_satisfaction"),
        ).filter(
            extract("year", VisitorSheet.visit_date) == year,
            extract("month", VisitorSheet.visit_date) == month,
        )

        if agent_id is not None:
            query = query.filter(VisitorSheet.agent_id == agent_id)

        result = query.first()

        return {
            "total_visits": int(result.total_visits) if result.total_visits else 0,
            "avg_satisfaction": int(result.avg_satisfaction) if result.avg_satisfaction else 0,
        }

    def get_intention_stats(self, agent_id: int | None = None) -> dict[str, int]:
        """Get statistics by intention level."""
        query = self.session.query(
            VisitorSheet.intention, func.count(VisitorSheet.id).label("count")
        ).group_by(VisitorSheet.intention)

        if agent_id is not None:
            query = query.filter(VisitorSheet.agent_id == agent_id)

        return {intention.value: count for intention, count in query.all()}

    def get_satisfaction_stats(
        self, property_id: int | None = None, agent_id: int | None = None
    ) -> dict[str, Any]:
        """Get satisfaction statistics."""
        query = self.session.query(
            func.count(VisitorSheet.id).label("total_visits"),
            func.avg(VisitorSheet.satisfaction).label("avg_satisfaction"),
        ).filter(VisitorSheet.satisfaction.isnot(None))

        if property_id is not None:
            query = query.filter(VisitorSheet.property_id == property_id)
        if agent_id is not None:
            query = query.filter(VisitorSheet.agent_id == agent_id)

        result = query.first()

        return {
            "total_visits": int(result.total_visits) if result.total_visits else 0,
            "avg_satisfaction": float(result.avg_satisfaction) if result.avg_satisfaction else 0.0,
        }

    def get_with_details(self, visitor_sheet_id: int) -> VisitorSheet | None:
        """Get visitor sheet with related entities (lead, property, agent) loaded."""
        return (
            self.session.query(VisitorSheet)
            .filter(VisitorSheet.id == visitor_sheet_id)
            .options(
                joinedload(VisitorSheet.lead),
                joinedload(VisitorSheet.property),
                joinedload(VisitorSheet.agent),
            )
            .first()
        )

    def get_by_criteria(
        self,
        skip: int = 0,
        limit: int = 20,
        lead_id: int | None = None,
        property_id: int | None = None,
        agent_id: int | None = None,
        start_date: date | None = None,
        end_date: date | None = None,
        intention: str | None = None,
    ) -> tuple[list[VisitorSheet], int]:
        """Get visitor sheets by criteria with pagination."""
        query = self.session.query(VisitorSheet)

        # Apply filters
        if lead_id is not None:
            query = query.filter(VisitorSheet.lead_id == lead_id)
        if property_id is not None:
            query = query.filter(VisitorSheet.property_id == property_id)
        if agent_id is not None:
            query = query.filter(VisitorSheet.agent_id == agent_id)
        if start_date is not None:
            query = query.filter(VisitorSheet.visit_date >= start_date)
        if end_date is not None:
            query = query.filter(VisitorSheet.visit_date <= end_date)
        if intention is not None:
            query = query.filter(VisitorSheet.intention == intention)

        # Count total
        total = query.count()

        # Get paginated results
        items = query.order_by(VisitorSheet.visit_date.desc()).offset(skip).limit(limit).all()

        return items, total
